const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
const readline = require('readline');
require('dotenv').config();

/**
 * 数据库部署脚本
 * 用于将本地数据库结构和更新同步到线上环境
 */

const rl = readline.createInterface({
  input: process.stdin,
  output: process.stdout
});

function question(prompt) {
  return new Promise(resolve => {
    rl.question(prompt, resolve);
  });
}

async function deployDatabase() {
  console.log('🚀 数据库部署工具');
  console.log('================\n');
  
  try {
    // 获取线上数据库配置
    console.log('📋 请输入线上数据库配置信息:');
    const prodConfig = {
      host: await question('数据库地址: '),
      port: parseInt(await question('端口 (默认3306): ') || '3306'),
      user: await question('用户名: '),
      password: await question('密码: '),
      database: await question(`数据库名 (默认${process.env.DB_NAME || 'llz_project'}): `) || process.env.DB_NAME || 'llz_project'
    };
    
    console.log('\n🔍 配置确认:');
    console.log(`主机: ${prodConfig.host}:${prodConfig.port}`);
    console.log(`数据库: ${prodConfig.database}`);
    console.log(`用户: ${prodConfig.user}`);
    
    const confirm = await question('\n确认配置正确? (y/N): ');
    if (confirm.toLowerCase() !== 'y') {
      console.log('❌ 部署已取消');
      process.exit(0);
    }
    
    // 测试连接
    console.log('\n🔌 测试数据库连接...');
    let connection;
    try {
      connection = await mysql.createConnection(prodConfig);
      console.log('✅ 连接成功!');
    } catch (error) {
      console.error('❌ 连接失败:', error.message);
      process.exit(1);
    }
    
    // 检查数据库是否存在
    console.log('\n📊 检查数据库状态...');
    const [databases] = await connection.execute('SHOW DATABASES');
    const dbExists = databases.some(row => Object.values(row)[0] === prodConfig.database);
    
    if (!dbExists) {
      console.log(`⚠️  数据库 '${prodConfig.database}' 不存在`);
      const createDb = await question('是否创建数据库? (y/N): ');
      if (createDb.toLowerCase() === 'y') {
        await connection.execute(`CREATE DATABASE \`${prodConfig.database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
        console.log('✅ 数据库创建成功');
      } else {
        console.log('❌ 部署已取消');
        process.exit(0);
      }
    }
    
    // 切换到目标数据库
    await connection.execute(`USE \`${prodConfig.database}\``);
    
    // 检查现有表
    const [tables] = await connection.execute('SHOW TABLES');
    const existingTables = tables.map(row => Object.values(row)[0]);
    
    if (existingTables.length > 0) {
      console.log(`\n⚠️  发现现有表: ${existingTables.join(', ')}`);
      const backup = await question('是否需要先备份现有数据? (Y/n): ');
      if (backup.toLowerCase() !== 'n') {
        console.log('💡 建议手动执行备份命令:');
        console.log(`mysqldump -h ${prodConfig.host} -u ${prodConfig.user} -p ${prodConfig.database} > backup_$(date +%Y%m%d_%H%M%S).sql`);
        const proceed = await question('\n备份完成后继续? (y/N): ');
        if (proceed.toLowerCase() !== 'y') {
          console.log('❌ 部署已取消');
          process.exit(0);
        }
      }
    }
    
    // 导出本地数据库结构
    console.log('\n📤 导出本地数据库结构...');
    const exportScript = require('./export_database_schema');
    await exportScript();
    
    // 查找最新的schema文件
    const outputDir = path.join(__dirname, '../output');
    const schemaFiles = fs.readdirSync(outputDir)
      .filter(file => file.startsWith('database_schema_') && file.endsWith('.sql'))
      .sort()
      .reverse();
    
    if (schemaFiles.length === 0) {
      console.error('❌ 未找到数据库结构文件');
      process.exit(1);
    }
    
    const latestSchema = path.join(outputDir, schemaFiles[0]);
    console.log(`📁 使用结构文件: ${schemaFiles[0]}`);
    
    // 读取并执行SQL文件
    console.log('\n🔄 应用数据库结构...');
    const sqlContent = fs.readFileSync(latestSchema, 'utf8');
    const sqlStatements = sqlContent
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt && !stmt.startsWith('--'));
    
    let successCount = 0;
    let errorCount = 0;
    
    for (const statement of sqlStatements) {
      try {
        await connection.execute(statement);
        successCount++;
      } catch (error) {
        console.warn(`⚠️  SQL执行警告: ${error.message}`);
        errorCount++;
      }
    }
    
    console.log(`✅ 结构同步完成: ${successCount} 成功, ${errorCount} 警告`);
    
    // 应用增量更新
    const updateScript = path.join(__dirname, 'update_payments_table.sql');
    if (fs.existsSync(updateScript)) {
      console.log('\n🔄 应用增量更新...');
      const updateSQL = fs.readFileSync(updateScript, 'utf8');
      const updateStatements = updateSQL
        .split(';')
        .map(stmt => stmt.trim())
        .filter(stmt => stmt && !stmt.startsWith('--'));
      
      for (const statement of updateStatements) {
        try {
          await connection.execute(statement);
          console.log('✅ 更新执行成功');
        } catch (error) {
          if (error.code === 'ER_DUP_FIELDNAME') {
            console.log('ℹ️  字段已存在，跳过');
          } else {
            console.warn(`⚠️  更新警告: ${error.message}`);
          }
        }
      }
    }
    
    // 验证部署结果
    console.log('\n🔍 验证部署结果...');
    const [finalTables] = await connection.execute('SHOW TABLES');
    const deployedTables = finalTables.map(row => Object.values(row)[0]);
    
    console.log(`✅ 部署完成! 共 ${deployedTables.length} 个表:`);
    deployedTables.forEach(table => console.log(`   - ${table}`));
    
    // 检查关键表结构
    const keyTables = ['users', 'contracts', 'payments'];
    for (const table of keyTables) {
      if (deployedTables.includes(table)) {
        const [columns] = await connection.execute(`DESCRIBE \`${table}\``);
        console.log(`\n📋 ${table} 表结构 (${columns.length} 字段):`);
        columns.forEach(col => {
          console.log(`   ${col.Field}: ${col.Type} ${col.Null === 'NO' ? 'NOT NULL' : 'NULL'}`);
        });
      }
    }
    
    console.log('\n🎉 数据库部署成功!');
    console.log('\n📝 后续步骤:');
    console.log('1. 更新线上服务器的 .env 文件');
    console.log('2. 上传微信支付证书文件');
    console.log('3. 重启应用服务');
    console.log('4. 测试API接口');
    
  } catch (error) {
    console.error('❌ 部署失败:', error.message);
    process.exit(1);
  } finally {
    rl.close();
    if (connection) {
      await connection.end();
    }
  }
}

// 执行部署
if (require.main === module) {
  deployDatabase();
}

module.exports = deployDatabase;